﻿Module mdlSQLFunctions
    '*// Deze module bevat enkele functies die het zo snel en eenvoudig mogelijk opzoeken van data zeer makkelijk maken
    '*// Bijvoorbeeld: iSite = DBLookup2("ObjectID", "SITE", "SiteName = '" & items(i)(1) & "'", fGetConnectionString)
    '*// Geeft zeer snel in een string de inhoud van de gevraagde cel terug.

    'Public Function DBLookup(ByVal sField As String, ByVal sTable As String, ByVal sWhere As String, ByVal sCNS As String) As ADODB.Recordset
    '    Dim cn As New ADODB.Connection()
    '    Dim rs As New ADODB.Recordset()
    '    Dim sSQL As String

    '    Try
    '        '*// Geeft recordset terug

    '        cn.ConnectionString = sCNS
    '        cn.Open()
    '        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    '        rs.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
    '        rs.LockType = ADODB.LockTypeEnum.adLockOptimistic
    '        sSQL = "SELECT " & sField & _
    '                " FROM " & sTable & _
    '                " WHERE " & sWhere

    '        mdlLOGGING.fLOG( "mdlSQL => DBLookup-SQL: " & sSQL, 100)

    '        rs.Open(sSQL, cn)
    '        Return rs
    '    Catch ex As Exception
    '        mdlLOGGING.fLOG( "mdlSQL => DBLOOKUP: " & ex.Message.ToString, 99)
    '        Return rs
    '    End Try

    'End Function

    Public Function fGetConnectionString() As String
        Try
            '*// Alle variabelen om de connectiestring te bouwen:
            Dim DataSourceFile As String = My.Settings.DataSourceFile
            Dim DataSourceVersion As String = My.Settings.DataSourceVersion
            'Dim bokpwd As String = Decrypt(My.Settings.PasswordBOK, "azertyuiop")

            '*// Geef de connectiestring terug:
            'Return "provider=" & bokprov & ";server=" & bokServer & ";database=" & bokDB & ";uid=" & bokUserID & ";pwd=" & bokpwd

            Return "Data Source=" & DataSourceFile & ";Version=" & DataSourceVersion & ";"

        Catch ex As Exception
            mdlLOGGING.fLOG("mdl_Logon => fGetConnectionString: " & ex.Message.ToString, 99)
            Return ""
        End Try
    End Function

    Public Function DBLookup(ByVal sField As String, ByVal sTable As String, ByVal sWhere As String, ByVal sCNS As String) As String
        Dim cn As New SQLite.SQLiteConnection ' ADODB.Connection()
        Dim da As New SQLite.SQLiteDataAdapter 'Dim rs As New ADODB.Recordset()
        Dim ds As New DataSet
        Dim sSQL As String

        '*// Geeft string terug
        Try

            'Return ""

            'Exit Function

            cn.ConnectionString = sCNS
            cn.Open()
            'rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            'rs.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
            'rs.LockType = ADODB.LockTypeEnum.adLockOptimistic
            sSQL = "SELECT " & sField & _
                    " FROM " & sTable & _
                    " WHERE " & sWhere

            mdlLOGGING.fLOG("mdlSQL => DBLookup-SQL: " & sSQL, 100)

            da = New SQLite.SQLiteDataAdapter(sSQL, cn)
            da.Fill(ds, sTable)

            'rs.Open(sSQL, cn)
            'If Not rs.EOF Then
            If ds.Tables(0).Rows.Count > 0 Then
                'Return rs.Fields(sField).Value.ToString
                Return ds.Tables(0).Rows(0)(sField).ToString
            Else
                Return ""
            End If

            cn.Close()

        Catch ex As Exception
            mdlLOGGING.fLOG("mdlSQL => DBLOOKUP: " & ex.Message.ToString, 99)
        End Try

        cn = Nothing
        Return ""

    End Function
End Module
